package utils;

import model.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Arrays;
import java.util.Random;
import java.util.Collections;


public class DBUtil {

    private static final String DB_URL = "jdbc:sqlite:mydatabase.db";

    // 建立数据库连接
    public static Connection connect() throws SQLException {
        return DriverManager.getConnection(DB_URL);
    }

    // 删除所有表的方法
    public static void dropAllTables() {
        String dropLogisticsInfoTableSql = "DROP TABLE IF EXISTS logistics_info";
        String dropGroupsTableSql = "DROP TABLE IF EXISTS groups";
        String dropCustomerGroupTableSql = "DROP TABLE IF EXISTS customer_group";

        try (Connection conn = connect();
             Statement stmt = conn.createStatement()) {

            stmt.executeUpdate(dropCustomerGroupTableSql);
            stmt.executeUpdate(dropLogisticsInfoTableSql);
            stmt.executeUpdate(dropGroupsTableSql);

            System.out.println("所有表已成功删除。");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    //检查分组是否存在
    public static boolean checkGroupExists(String groupName) {
        String query = "SELECT COUNT(*) FROM groups WHERE name = ?";
        try (Connection conn = connect();
             PreparedStatement stmt = conn.prepareStatement(query)) {

            stmt.setString(1, groupName);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


    // 创建表的方法，包含多对多的关联表
    public static void createTableIfNotExists() {
        //分组表
        String createGroupsTableSql = "CREATE TABLE IF NOT EXISTS groups (\n" +
                "    group_id INTEGER PRIMARY KEY AUTOINCREMENT, -- 分组唯一标识\n" +
                "    name VARCHAR(200) NOT NULL UNIQUE -- 分组名称，唯一约束\n" +
                ");";

        //客户表
        String createCustomersTableSql = "CREATE TABLE IF NOT EXISTS customers (\n" +
                "    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
                "    customer_name VARCHAR(200) NOT NULL\n" +
                ");";

        //属性定义表
        String createAttributesTableSql = "CREATE TABLE IF NOT EXISTS attributes (\n" +
                "    attribute_id INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
                "    attribute_key VARCHAR(100) NOT NULL UNIQUE,\n" +
                "    display_name VARCHAR(200) NOT NULL,\n" +
                "    data_type INTEGER NOT NULL,\n" +
                "    default_value TEXT\n" +
                ");";

        //客户属性值表
        String createCustomerAttributeValuesTableSql = "CREATE TABLE IF NOT EXISTS customer_attribute_values (\n" +
                "    customer_id INTEGER,\n" +
                "    attribute_id INTEGER,\n" +
                "    attribute_value TEXT,\n" +
                "    is_highlighted BOOLEAN DEFAULT 0,\n" +
                "    PRIMARY KEY (customer_id, attribute_id),\n" +
                "    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,\n" +
                "    FOREIGN KEY (attribute_id) REFERENCES attributes(attribute_id) ON DELETE CASCADE\n" +
                ");";

        //用户分组关系表
        String createCustomerGroupTableSql = "CREATE TABLE IF NOT EXISTS customer_group (\n" +
                "    customer_id INTEGER,\n" +
                "    group_id INTEGER,\n" +
                "    PRIMARY KEY (customer_id, group_id),\n" +
                "    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,\n" +
                "    FOREIGN KEY (group_id) REFERENCES groups(group_id) ON DELETE CASCADE\n" +
                ");";

        //物流运输要求表
        String createLogisticsRequirementTableSql = "CREATE TABLE IF NOT EXISTS logistics_requirement (\n" +
                "    serial_no INTEGER PRIMARY KEY AUTOINCREMENT, -- 修改为自增主键\n" +
                "    item VARCHAR(200), -- 项目\n" +
                "    requirement TEXT, -- 具体要求\n" +
                "    proEffi VARCHAR(200), --处理失效\n" +
                "    responsible_person VARCHAR(100), -- 责任人\n" +
                "    supervisor VARCHAR(100) -- 监管人\n" +
                ");";

        try (Connection conn = connect();
             PreparedStatement pstmt1 = conn.prepareStatement(createCustomersTableSql);
             PreparedStatement pstmt2 = conn.prepareStatement(createGroupsTableSql);
             PreparedStatement pstmt3 = conn.prepareStatement(createCustomerGroupTableSql);
             PreparedStatement pstmt4 = conn.prepareStatement(createLogisticsRequirementTableSql);
             PreparedStatement pstmt5 = conn.prepareStatement(createAttributesTableSql);
             PreparedStatement pstmt6 = conn.prepareStatement(createCustomerAttributeValuesTableSql)) {

            pstmt1.executeUpdate();
            pstmt2.executeUpdate();
            pstmt3.executeUpdate();
            pstmt4.executeUpdate();
            pstmt5.executeUpdate();
            pstmt6.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 插入客户信息的方法
    public static void insertData(LogisticsInfo logisticsInfo) {
        String sql = "INSERT INTO logistics_info (name, address, km, contacts, time_input, " +
                "truck_temp_min, truck_temp_max, product_temp_min, product_temp_max, " +
                "measure_method, equipment_type, equipment_num, open_box, " +
                "tray_transport, unloading_service, unloading_cost, appointment, " +
                "document_need, stamp_confirm, replace_box, delay_accept, other_needs, " +
                "is_first_battle_arrival) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, logisticsInfo.getName());
            pstmt.setString(2, logisticsInfo.getAddress());
            pstmt.setInt(3, logisticsInfo.getKm());
            pstmt.setString(4, logisticsInfo.getContacts());
            pstmt.setString(5, logisticsInfo.getTimeInput());
            pstmt.setInt(6, logisticsInfo.getTruckTempMin());
            pstmt.setInt(7, logisticsInfo.getTruckTempMax());
            pstmt.setInt(8, logisticsInfo.getProductTempMin());
            pstmt.setInt(9, logisticsInfo.getProductTempMax());
            pstmt.setString(10, logisticsInfo.getMeasureMethod());
            pstmt.setString(11, logisticsInfo.getEquipmentType());
            pstmt.setString(12, logisticsInfo.getEquipmentNum());
            pstmt.setString(13, logisticsInfo.getOpenBox());
            pstmt.setString(14, logisticsInfo.getTrayTransport());
            pstmt.setString(15, logisticsInfo.getUnloadingService());
            pstmt.setString(16, logisticsInfo.getUnloadingCost());
            pstmt.setString(17, logisticsInfo.getAppointment());
            pstmt.setString(18, logisticsInfo.getDocumentNeed());
            pstmt.setString(19, logisticsInfo.getStampConfirm());
            pstmt.setString(20, logisticsInfo.getReplaceBox());
            pstmt.setString(21, logisticsInfo.getDelayAccept());
            pstmt.setString(22, logisticsInfo.getOtherNeeds());
            pstmt.setString(23, logisticsInfo.getIsFirstBattleArrival());

            pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    // 插入分组的方法
    public static void insertGroup(Group group) {
        if (group == null || group.getName() == null || group.getName().isEmpty()) {
            throw new IllegalArgumentException("分组名称不能为空");
        }

        String sql = "INSERT INTO groups (name) VALUES (?)";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, group.getName());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 插入客户与分组的关联关系
    public static void insertCustomerToGroup(int customerId, int groupId) {
        String sql = "INSERT INTO customer_group (customer_id, group_id) VALUES (?, ?)";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, customerId);
            pstmt.setInt(2, groupId);
            pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 查询所有客户的方法
    public static List<LogisticsInfo> selectAllCustomers() {
        List<LogisticsInfo> customersList = new ArrayList<>();
        String sql = "SELECT * FROM logistics_info";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                LogisticsInfo customer = new LogisticsInfo(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("address"),
                        rs.getInt("km"),
                        rs.getString("contacts"),
                        rs.getString("time_input"),
                        rs.getString("is_first_battle_arrival"),
                        rs.getInt("truck_temp_min"),
                        rs.getInt("truck_temp_max"),
                        rs.getInt("product_temp_min"),
                        rs.getInt("product_temp_max"),
                        rs.getString("measure_method"),
                        rs.getString("equipment_type"),
                        rs.getString("equipment_num"),
                        rs.getString("open_box"),
                        rs.getString("tray_transport"),
                        rs.getString("unloading_service"),
                        rs.getString("unloading_cost"),
                        rs.getString("appointment"),
                        rs.getString("document_need"),
                        rs.getString("stamp_confirm"),
                        rs.getString("replace_box"),
                        rs.getString("delay_accept"),
                        rs.getString("other_needs")
                );
                customersList.add(customer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return customersList;
    }


    // 删除客户的方法
    public static void deleteCustomer(int customerId) {
        String sql = "DELETE FROM logistics_info WHERE id = ?";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, customerId);
            pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 删除分组的方法，并删除关联关系
    public static void deleteGroup(int groupId) {
        String deleteGroupSql = "DELETE FROM groups WHERE group_id = ?";
        String deleteCustomerGroupSql = "DELETE FROM customer_group WHERE group_id = ?";

        try (Connection conn = connect()) {
            conn.setAutoCommit(false);  // 开启事务

            try (PreparedStatement deleteGroupStmt = conn.prepareStatement(deleteGroupSql);
                 PreparedStatement deleteCustomerGroupStmt = conn.prepareStatement(deleteCustomerGroupSql)) {

                // 删除关联关系
                deleteCustomerGroupStmt.setInt(1, groupId);
                deleteCustomerGroupStmt.executeUpdate();

                // 删除分组
                deleteGroupStmt.setInt(1, groupId);
                deleteGroupStmt.executeUpdate();

                conn.commit();  // 提交事务
            } catch (SQLException e) {
                conn.rollback();  // 如果有异常，回滚事务
                e.printStackTrace();
            } finally {
                conn.setAutoCommit(true);  // 恢复自动提交
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 查询某个分组下的客户
    public static List<Customer> selectCustomersByGroupId(int groupId) {
        List<Customer> customersList = new ArrayList<>();
        String sql = "SELECT DISTINCT c.* FROM customers c " +
                "JOIN customer_group cg ON c.customer_id = cg.customer_id " +
                "WHERE cg.group_id = ?";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, groupId);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Customer customer = new Customer();
                    customer.setCustomerId(rs.getInt("customer_id"));
                    customer.setCustomerName(rs.getString("customer_name"));
                    
                    // 加载客户的所有属性值
                    loadCustomerAttributes(customer);
                    
                    customersList.add(customer);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return customersList;
    }

    // 加载客户的所有属性值和高亮状态
    private static void loadCustomerAttributes(Customer customer) {
        String sql = "SELECT a.attribute_id, a.attribute_key, cav.string_value, cav.is_heightlight " +
                "FROM customer_attribute_values cav " +
                "JOIN attributes a ON cav.attribute_id = a.attribute_id " +
                "WHERE cav.customer_id = ?";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, customer.getCustomerId());

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    int attributeId = rs.getInt("attribute_id");
                    String attributeValue = rs.getString("string_value");
                    boolean isHighlighted = rs.getBoolean("is_heightlight");

                    customer.setAttributeValue(attributeId, attributeValue);
                    customer.setAttributeHighlight(attributeId, isHighlighted);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 查询某个客户所属的所有分组
    public static List<Group> selectGroupsByCustomerId(int customerId) {
        List<Group> groupList = new ArrayList<>();
        String sql = "SELECT g.* FROM groups g " +
                "JOIN customer_group cg ON g.group_id = cg.group_id " +
                "WHERE cg.customer_id = ?";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, customerId);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Group group = new Group(rs.getInt("group_id"), rs.getString("name"));
                    groupList.add(group);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return groupList;
    }

    // 从分组中移除客户
    public static void removeCustomerFromGroup(int customerId, int groupId) {
        String sql = "DELETE FROM customer_group WHERE customer_id = ? AND group_id = ?";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, customerId);
            pstmt.setInt(2, groupId);
            pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 删除某个客户的所有分组关联
    public static void removeAllGroupsForCustomer(int customerId) {
        String sql = "DELETE FROM customer_group WHERE customer_id = ?";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, customerId);
            pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 查询所有分组的方法
    public static List<Group> selectAllGroups() {
        List<Group> groupList = new ArrayList<>();
        String sql = "SELECT * FROM groups";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                Group group = new Group(rs.getInt("group_id"), rs.getString("name"));
                groupList.add(group);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return groupList;
    }

    // 插入CustomerGroup关联数据的方法
    public static void insertCustomerGroup(CustomerGroup customerGroup) {
        String sql = "INSERT INTO customer_group (customer_id, group_id) VALUES (?, ?)";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, customerGroup.getCustomerId());
            pstmt.setInt(2, customerGroup.getGroupId());

            pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 根据groupId删除CustomerGroup关联数据的方法
    public static void deleteCustomerGroupByGroupId(int groupId) {
        String sql = "DELETE FROM customer_group WHERE group_id = ?";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, groupId);
            pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 根据customerId和groupId删除特定的关联数据
    public static void deleteCustomerGroup(int customerId, int groupId) {
        String sql = "DELETE FROM customer_group WHERE customer_id = ? AND group_id = ?";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, customerId);
            pstmt.setInt(2, groupId);
            pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 根据ID查询单个客户信息
    public static LogisticsInfo selectCustomerById(int customerId) {
        String sql = "SELECT * FROM logistics_info WHERE id = ?";
        LogisticsInfo customer = null;

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, customerId);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                customer = new LogisticsInfo(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("address"),
                        rs.getInt("km"),
                        rs.getString("contacts"),
                        rs.getString("time_input"),
                        rs.getString("is_first_battle_arrival"),
                        rs.getInt("truck_temp_min"),
                        rs.getInt("truck_temp_max"),
                        rs.getInt("product_temp_min"),
                        rs.getInt("product_temp_max"),
                        rs.getString("measure_method"),
                        rs.getString("equipment_type"),
                        rs.getString("equipment_num"),
                        rs.getString("open_box"),
                        rs.getString("tray_transport"),
                        rs.getString("unloading_service"),
                        rs.getString("unloading_cost"),
                        rs.getString("appointment"),
                        rs.getString("document_need"),
                        rs.getString("stamp_confirm"),
                        rs.getString("replace_box"),
                        rs.getString("delay_accept"),
                        rs.getString("other_needs")
                );

            }
        } catch (SQLException e) {
            System.out.println("查询客户信息时出错: " + e.getMessage());
            e.printStackTrace();
        }

        return customer;
    }



    // 更新客户信息的方法
    public static void updateData(LogisticsInfo logisticsInfo) {
        String sql = "UPDATE logistics_info SET name = ?, address = ?, km = ?, contacts = ?, time_input = ?, " +
                "truck_temp_min = ?, truck_temp_max = ?, product_temp_min = ?, product_temp_max = ?, " +
                "measure_method = ?, equipment_type = ?, equipment_num = ?, open_box = ?, " +
                "tray_transport = ?, unloading_service = ?, unloading_cost = ?, appointment = ?, " +
                "document_need = ?, stamp_confirm = ?, replace_box = ?, delay_accept = ?, " +
                "other_needs = ?, is_first_battle_arrival = ? WHERE id = ?";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, logisticsInfo.getName());
            pstmt.setString(2, logisticsInfo.getAddress());
            pstmt.setInt(3, logisticsInfo.getKm());
            pstmt.setString(4, logisticsInfo.getContacts());
            pstmt.setString(5, logisticsInfo.getTimeInput());
            pstmt.setInt(6, logisticsInfo.getTruckTempMin());
            pstmt.setInt(7, logisticsInfo.getTruckTempMax());
            pstmt.setInt(8, logisticsInfo.getProductTempMin());
            pstmt.setInt(9, logisticsInfo.getProductTempMax());
            pstmt.setString(10, logisticsInfo.getMeasureMethod());
            pstmt.setString(11, logisticsInfo.getEquipmentType());
            pstmt.setString(12, logisticsInfo.getEquipmentNum());
            pstmt.setString(13, logisticsInfo.getOpenBox());
            pstmt.setString(14, logisticsInfo.getTrayTransport());
            pstmt.setString(15, logisticsInfo.getUnloadingService());
            pstmt.setString(16, logisticsInfo.getUnloadingCost());
            pstmt.setString(17, logisticsInfo.getAppointment());
            pstmt.setString(18, logisticsInfo.getDocumentNeed());
            pstmt.setString(19, logisticsInfo.getStampConfirm());
            pstmt.setString(20, logisticsInfo.getReplaceBox());
            pstmt.setString(21, logisticsInfo.getDelayAccept());
            pstmt.setString(22, logisticsInfo.getOtherNeeds());
            pstmt.setString(23, logisticsInfo.getIsFirstBattleArrival());
            pstmt.setInt(24, logisticsInfo.getId());

            pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    // 插入物流运输要求
    public static void insertLogisticsRequirement(LogisticsRequirement requirement) {
        String sql = "INSERT INTO logistics_requirement (item, requirement, proEffi,responsible_person, supervisor) "
                + "VALUES (?, ?, ?, ?,?)";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, requirement.getItem());
            pstmt.setString(2, requirement.getRequirement());
            pstmt.setString(3, requirement.getProEffi());
            pstmt.setString(4, requirement.getResponsiblePerson());
            pstmt.setString(5, requirement.getSupervisor());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("插入物流运输要求时出错: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // 查询所有物流运输要求
    public static List<LogisticsRequirement> getAllLogisticsRequirements() {
        List<LogisticsRequirement> requirements = new ArrayList<>();
        String sql = "SELECT * FROM logistics_requirement ORDER BY serial_no";

        try (Connection conn = connect();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                LogisticsRequirement requirement = new LogisticsRequirement(
                       rs.getInt("serial_no"),
                    rs.getString("item"),
                    rs.getString("requirement"),
                    rs.getString("proEffi"),
                    rs.getString("responsible_person"),
                    rs.getString("supervisor")
                );
                requirements.add(requirement);
            }
        } catch (SQLException e) {
            System.out.println("查询物流运输要求时出错: " + e.getMessage());
            e.printStackTrace();
        }
        return requirements;
    }

    public static LogisticsRequirement getLogisticsRequirementByNo(int serialNo) {
        String sql = "SELECT * FROM logistics_requirement where serial_no =  " + serialNo;
        LogisticsRequirement requirement = new LogisticsRequirement();
        try (Connection conn = connect();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                requirement = new LogisticsRequirement(
                        rs.getInt("serial_no"),
                        rs.getString("item"),
                        rs.getString("requirement"),
                        rs.getString("proEffi"),
                        rs.getString("responsible_person"),
                        rs.getString("supervisor")
                );
            }
        } catch (SQLException e) {
            System.out.println("查询物流运输要求时出错: " + e.getMessage());
            e.printStackTrace();
        }
        return requirement;
    }


    // 更新物流运输要求
    public static void updateLogisticsRequirement(LogisticsRequirement requirement) {
        String sql = "UPDATE logistics_requirement SET item = ?, requirement = ?, "
                + "responsible_person = ?, supervisor = ? WHERE serial_no = ?";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, requirement.getItem());
            pstmt.setString(2, requirement.getRequirement());
            pstmt.setString(3, requirement.getResponsiblePerson());
            pstmt.setString(4, requirement.getSupervisor());
            pstmt.setInt(5, requirement.getSerialNo());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("更新物流运输要求时出错: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // 删除物流运输要求
    public static void deleteLogisticsRequirement(int serialNo) {
        String sql = "DELETE FROM logistics_requirement WHERE serial_no = ?";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, serialNo);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("删除物流运输要求时出错: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private static void generateLogisticsInfo(int count) {
        List<String> customerNames = Arrays.asList(
            "永辉超市东莞配送中心",
            "华润万家深圳物流中心",
            "大润发广州配送中心",
            "沃尔玛佛山生鲜仓",
            "天虹商场中山物流中心"
        );
        List<String> addresses = Arrays.asList(
            "广东省东莞市麻涌镇新沙路普洛斯东莞新沙物流园",
            "深圳市宝安区福永镇怀德港物流中心",
            "广州市白云区石井镇鸦岗物流园",
            "佛山市南海区狮山镇小塘物流园",
            "中山市火炬开发区东镇东二路物流园"
        );
        
        List<String> contacts = Arrays.asList(
            "张经理 13800138000",
            "李主管 13900139000",
            "王主任 13700137000",
            "陈经理 13600136000",
            "刘主管 13500135000"
        );
        
        List<String> timeInputs = Arrays.asList(
            "每日上午9:00前到达",
            "每日14:00前到达",
            "每周一、三、五上午10:00前到达",
            "每天早上7:00-9:00到达",
            "工作日下午13:00-15:00到达"
        );

        List<String> measureMethods = Arrays.asList(
            "装车前开箱抽检",
            "到达后开箱抽检",
            "全程温度监控",
            "装卸车双重抽检"
        );

        List<String> equipmentTypes = Arrays.asList(
            "冷王",
            "开利",
            "顶置独立机组",
            "德利",
            "日本三菱"
        );

        try {
            Connection conn = DBUtil.connect();
            String sql = "INSERT INTO logistics_info (name, address, km, contacts, time_input, " +
                        "truck_temp_min, truck_temp_max, product_temp_min, product_temp_max, " +
                        "measure_method, equipment_type, equipment_num, open_box, " +
                        "tray_transport, unloading_service, unloading_cost, appointment, " +
                        "document_need, stamp_confirm, replace_box, delay_accept, other_needs, " +
                        "is_first_battle_arrival) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            Random random = new Random();

            for (int i = 0; i < count; i++) {
                int index = i % customerNames.size();
                pstmt.setString(1, customerNames.get(index));
                pstmt.setString(2, addresses.get(index));
                pstmt.setInt(3, 50 + random.nextInt(250)); // 50-300km
                pstmt.setString(4, contacts.get(index));
                pstmt.setString(5, timeInputs.get(random.nextInt(timeInputs.size())));
                
                // 温度设置 (根据不同类型的商品设置不同的温度范围)
                int baseTemp = random.nextInt(3) * 5; // 0, 5, 10作为基础温度
                pstmt.setInt(6, baseTemp);     // truck_temp_min
                pstmt.setInt(7, baseTemp + 4); // truck_temp_max
                pstmt.setInt(8, baseTemp);     // product_temp_min
                pstmt.setInt(9, baseTemp + 4); // product_temp_max
                
                pstmt.setString(10, measureMethods.get(random.nextInt(measureMethods.size())));
                pstmt.setString(11, equipmentTypes.get(random.nextInt(equipmentTypes.size())));
                pstmt.setString(12, (1 + random.nextInt(3)) + "台"); // 1-3台设备
                pstmt.setString(13, random.nextBoolean() ? "是" : "否"); // 是否开箱测温
                pstmt.setString(14, random.nextBoolean() ? "是" : "否"); // 是否带托运输
                pstmt.setString(15, random.nextBoolean() ? "是" : "否"); // 是否提供卸货服务
                pstmt.setString(16, (30 + random.nextInt(21)) + "元/吨"); // 30-50元/吨
                pstmt.setString(17, random.nextBoolean() ? "是" : "否"); // 是否需要预约
                pstmt.setString(18, "装车单,签收单" + (random.nextBoolean() ? ",温度记录单" : "")); // 随车单据需求
                pstmt.setString(19, random.nextBoolean() ? "是" : "否"); // 是否盖章签收确认
                pstmt.setString(20, random.nextBoolean() ? "是" : "否"); // 是否能更换无批次纸箱
                pstmt.setString(21, random.nextBoolean() ? "是" : "否"); // 异常晚到是否可延迟收货
                pstmt.setString(22, "无特殊要求"); // 其他需求
                pstmt.setString(23, random.nextBoolean() ? "是" : "否"); // 是否首战到达

                pstmt.executeUpdate();
                
                // 获取自增主键
                ResultSet generatedKeys = pstmt.getGeneratedKeys();
                if (generatedKeys.next()) {
                    int logisticsId = generatedKeys.getInt(1);
                    
                    // 为每条物流信息生成随机标红信息
                    LogisticsHighlight highlight = new LogisticsHighlight(logisticsId);
                    
                    // 随机选择2-5个字段进行标红
                    int highlightCount = 2 + random.nextInt(4); // 2到5个标红字段
                    List<Integer> fieldIndices = new ArrayList<>();
                    for (int j = 0; j < 23; j++) {
                        fieldIndices.add(j);
                    }
                    Collections.shuffle(fieldIndices);
                    
                    // 设置随机标红字段
                    for (int j = 0; j < highlightCount; j++) {
                        int fieldIndex = fieldIndices.get(j);
                        switch (fieldIndex) {
                            case 0: highlight.setNameHighlight(1); break;
                            case 1: highlight.setAddressHighlight(1); break;
                            case 2: highlight.setKmHighlight(1); break;
                            case 3: highlight.setContactsHighlight(1); break;
                            case 4: highlight.setTimeInputHighlight(1); break;
                            case 5: highlight.setIsFirstBattleArrivalHighlight(1); break;
                            case 6: highlight.setTruckTempMinHighlight(1); break;
                            case 7: highlight.setTruckTempMaxHighlight(1); break;
                            case 8: highlight.setProductTempMinHighlight(1); break;
                            case 9: highlight.setProductTempMaxHighlight(1); break;
                            case 10: highlight.setMeasureMethodHighlight(1); break;
                            case 11: highlight.setEquipmentTypeHighlight(1); break;
                            case 12: highlight.setEquipmentNumHighlight(1); break;
                            case 13: highlight.setOpenBoxHighlight(1); break;
                            case 14: highlight.setTrayTransportHighlight(1); break;
                            case 15: highlight.setUnloadingServiceHighlight(1); break;
                            case 16: highlight.setUnloadingCostHighlight(1); break;
                            case 17: highlight.setAppointmentHighlight(1); break;
                            case 18: highlight.setDocumentNeedHighlight(1); break;
                            case 19: highlight.setStampConfirmHighlight(1); break;
                            case 20: highlight.setReplaceBoxHighlight(1); break;
                            case 21: highlight.setDelayAcceptHighlight(1); break;
                            case 22: highlight.setOtherNeedsHighlight(1); break;
                        }
                    }
                    
                    // 保存标红信息
                    insertLogisticsHighlight(highlight);
                }
            }
            System.out.println("成功生成 " + count + " 条冷链物流配送需求数据及其标红信息");
        } catch (SQLException e) {
            System.out.println("生成数据失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // 插入标红信息的方法
    public static void insertLogisticsHighlight(LogisticsHighlight highlight) {
        String sql = "INSERT INTO logistics_highlight (logistics_id, name_highlight, address_highlight, km_highlight, " +
                "contacts_highlight, time_input_highlight, is_first_battle_arrival_highlight, truck_temp_min_highlight, " +
                "truck_temp_max_highlight, product_temp_min_highlight, product_temp_max_highlight, measure_method_highlight, " +
                "equipment_type_highlight, equipment_num_highlight, open_box_highlight, tray_transport_highlight, " +
                "unloading_service_highlight, unloading_cost_highlight, appointment_highlight, document_need_highlight, " +
                "stamp_confirm_highlight, replace_box_highlight, delay_accept_highlight, other_needs_highlight) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, highlight.getLogisticsId());
            pstmt.setInt(2, highlight.getNameHighlight());
            pstmt.setInt(3, highlight.getAddressHighlight());
            pstmt.setInt(4, highlight.getKmHighlight());
            pstmt.setInt(5, highlight.getContactsHighlight());
            pstmt.setInt(6, highlight.getTimeInputHighlight());
            pstmt.setInt(7, highlight.getIsFirstBattleArrivalHighlight());
            pstmt.setInt(8, highlight.getTruckTempMinHighlight());
            pstmt.setInt(9, highlight.getTruckTempMaxHighlight());
            pstmt.setInt(10, highlight.getProductTempMinHighlight());
            pstmt.setInt(11, highlight.getProductTempMaxHighlight());
            pstmt.setInt(12, highlight.getMeasureMethodHighlight());
            pstmt.setInt(13, highlight.getEquipmentTypeHighlight());
            pstmt.setInt(14, highlight.getEquipmentNumHighlight());
            pstmt.setInt(15, highlight.getOpenBoxHighlight());
            pstmt.setInt(16, highlight.getTrayTransportHighlight());
            pstmt.setInt(17, highlight.getUnloadingServiceHighlight());
            pstmt.setInt(18, highlight.getUnloadingCostHighlight());
            pstmt.setInt(19, highlight.getAppointmentHighlight());
            pstmt.setInt(20, highlight.getDocumentNeedHighlight());
            pstmt.setInt(21, highlight.getStampConfirmHighlight());
            pstmt.setInt(22, highlight.getReplaceBoxHighlight());
            pstmt.setInt(23, highlight.getDelayAcceptHighlight());
            pstmt.setInt(24, highlight.getOtherNeedsHighlight());

            pstmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("插入标红信息时出错: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // 更新标红信息的方法
    public static void updateLogisticsHighlight(LogisticsHighlight highlight) {
        String sql = "UPDATE logistics_highlight SET name_highlight = ?, address_highlight = ?, km_highlight = ?, " +
                "contacts_highlight = ?, time_input_highlight = ?, is_first_battle_arrival_highlight = ?, " +
                "truck_temp_min_highlight = ?, truck_temp_max_highlight = ?, product_temp_min_highlight = ?, " +
                "product_temp_max_highlight = ?, measure_method_highlight = ?, equipment_type_highlight = ?, " +
                "equipment_num_highlight = ?, open_box_highlight = ?, tray_transport_highlight = ?, " +
                "unloading_service_highlight = ?, unloading_cost_highlight = ?, appointment_highlight = ?, " +
                "document_need_highlight = ?, stamp_confirm_highlight = ?, replace_box_highlight = ?, " +
                "delay_accept_highlight = ?, other_needs_highlight = ? WHERE logistics_id = ?";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, highlight.getNameHighlight());
            pstmt.setInt(2, highlight.getAddressHighlight());
            pstmt.setInt(3, highlight.getKmHighlight());
            pstmt.setInt(4, highlight.getContactsHighlight());
            pstmt.setInt(5, highlight.getTimeInputHighlight());
            pstmt.setInt(6, highlight.getIsFirstBattleArrivalHighlight());
            pstmt.setInt(7, highlight.getTruckTempMinHighlight());
            pstmt.setInt(8, highlight.getTruckTempMaxHighlight());
            pstmt.setInt(9, highlight.getProductTempMinHighlight());
            pstmt.setInt(10, highlight.getProductTempMaxHighlight());
            pstmt.setInt(11, highlight.getMeasureMethodHighlight());
            pstmt.setInt(12, highlight.getEquipmentTypeHighlight());
            pstmt.setInt(13, highlight.getEquipmentNumHighlight());
            pstmt.setInt(14, highlight.getOpenBoxHighlight());
            pstmt.setInt(15, highlight.getTrayTransportHighlight());
            pstmt.setInt(16, highlight.getUnloadingServiceHighlight());
            pstmt.setInt(17, highlight.getUnloadingCostHighlight());
            pstmt.setInt(18, highlight.getAppointmentHighlight());
            pstmt.setInt(19, highlight.getDocumentNeedHighlight());
            pstmt.setInt(20, highlight.getStampConfirmHighlight());
            pstmt.setInt(21, highlight.getReplaceBoxHighlight());
            pstmt.setInt(22, highlight.getDelayAcceptHighlight());
            pstmt.setInt(23, highlight.getOtherNeedsHighlight());
            pstmt.setInt(24, highlight.getLogisticsId());

            pstmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("更新标红信息时出错: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // 根据logistics_id查询标红信息
    public static LogisticsHighlight getLogisticsHighlightByLogisticsId(int logisticsId) {
        String sql = "SELECT * FROM logistics_highlight WHERE logistics_id = ?";
        LogisticsHighlight highlight = null;

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, logisticsId);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                highlight = new LogisticsHighlight(
                        rs.getInt("highlight_id"),
                        rs.getInt("logistics_id"),
                        rs.getInt("name_highlight"),
                        rs.getInt("address_highlight"),
                        rs.getInt("km_highlight"),
                        rs.getInt("contacts_highlight"),
                        rs.getInt("time_input_highlight"),
                        rs.getInt("is_first_battle_arrival_highlight"),
                        rs.getInt("truck_temp_min_highlight"),
                        rs.getInt("truck_temp_max_highlight"),
                        rs.getInt("product_temp_min_highlight"),
                        rs.getInt("product_temp_max_highlight"),
                        rs.getInt("measure_method_highlight"),
                        rs.getInt("equipment_type_highlight"),
                        rs.getInt("equipment_num_highlight"),
                        rs.getInt("open_box_highlight"),
                        rs.getInt("tray_transport_highlight"),
                        rs.getInt("unloading_service_highlight"),
                        rs.getInt("unloading_cost_highlight"),
                        rs.getInt("appointment_highlight"),
                        rs.getInt("document_need_highlight"),
                        rs.getInt("stamp_confirm_highlight"),
                        rs.getInt("replace_box_highlight"),
                        rs.getInt("delay_accept_highlight"),
                        rs.getInt("other_needs_highlight")
                );
            } else {
                // 如果没有找到记录，则创建一个新的默认对象
                highlight = new LogisticsHighlight(logisticsId);
            }
        } catch (SQLException e) {
            System.out.println("查询标红信息时出错: " + e.getMessage());
            e.printStackTrace();
        }

        return highlight;
    }

    // 删除标红信息的方法
    public static void deleteLogisticsHighlight(int logisticsId) {
        String sql = "DELETE FROM logistics_highlight WHERE logistics_id = ?";

        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, logisticsId);
            pstmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("删除标红信息时出错: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // 检查标红信息是否存在
    public static boolean checkLogisticsHighlightExists(int logisticsId) {
        String sql = "SELECT COUNT(*) FROM logistics_highlight WHERE logistics_id = ?";
        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, logisticsId);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            System.out.println("检查标红信息时出错: " + e.getMessage());
            e.printStackTrace();
        }
        return false;
    }

    // 保存或更新标红信息（如果存在则更新，不存在则插入）
    public static void saveOrUpdateLogisticsHighlight(LogisticsHighlight highlight) {
        if (checkLogisticsHighlightExists(highlight.getLogisticsId())) {
            updateLogisticsHighlight(highlight);
        } else {
            insertLogisticsHighlight(highlight);
        }
    }

    // 获取最后插入的物流信息记录
    public static LogisticsInfo getLastInsertedLogisticsInfo() {
        String sql = "SELECT * FROM logistics_info ORDER BY id DESC LIMIT 1";
        
        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                LogisticsInfo info = new LogisticsInfo();
                info.setId(rs.getInt("id"));
                info.setName(rs.getString("name"));
                info.setAddress(rs.getString("address"));
                info.setKm(rs.getInt("km"));
                info.setContacts(rs.getString("contacts"));
                info.setTimeInput(rs.getString("time_input"));
                info.setIsFirstBattleArrival(rs.getString("is_first_battle_arrival"));
                info.setTruckTempMin(rs.getInt("truck_temp_min"));
                info.setTruckTempMax(rs.getInt("truck_temp_max"));
                info.setProductTempMin(rs.getInt("product_temp_min"));
                info.setProductTempMax(rs.getInt("product_temp_max"));
                info.setMeasureMethod(rs.getString("measure_method"));
                info.setEquipmentType(rs.getString("equipment_type"));
                info.setEquipmentNum(rs.getString("equipment_num"));
                info.setOpenBox(rs.getString("open_box"));
                info.setTrayTransport(rs.getString("tray_transport"));
                info.setUnloadingService(rs.getString("unloading_service"));
                info.setUnloadingCost(rs.getString("unloading_cost"));
                info.setAppointment(rs.getString("appointment"));
                info.setDocumentNeed(rs.getString("document_need"));
                info.setStampConfirm(rs.getString("stamp_confirm"));
                info.setReplaceBox(rs.getString("replace_box"));
                info.setDelayAccept(rs.getString("delay_accept"));
                info.setOtherNeeds(rs.getString("other_needs"));
                return info;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}
